﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Entity;
using Command;
namespace DAL
{
    public class L_BooksDAL
    {
        //图书类别信息展示
        public static List<BookType> SeleteMessage()
        {
            string sql = string.Format("select Top(15)*from BookType");
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookType> UI = new List<Entity.BookType>();
            while (sdr.Read())
            {
                BookType Bt = new BookType();
                Bt.TypeName = (string)sdr["TypeName"];
                Bt.Note = sdr["Note"].ToString();
                Bt.state = (int)sdr["state"];
                Bt.TypeID = (int)sdr["TypeID"];
                UI.Add(Bt);
            }
            sdr.Close();
            return UI;
        }
        //添加图书类别信息
        public static void AddType(BookType bt)
        {
            string sql = string.Format("insert into BookType values('{0}','{1}','0','{2}')", bt.TypeName, bt.Note, bt.state);
            DBHelper.Adddata(sql);
        }
        //添加图书信息
        public static void AddBook(BookInformatica BF)
        {
            string sql = string.Format("insert into BookInformatica(BookName, BookAuthor, BookPress, BookTime, BookPrice, TypeID, BookState, BookDescribe, BookPicture,BookNumber) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", BF.BookName, BF.BookAuthor, BF.BookPress, BF.BookTime, BF.BookPrice, BF.TypeID, BF.BookState, BF.BookDescribe, BF.BookPicture,BF.BookNumber);
            DBHelper.Adddata(sql);
            string sql1 = "select *  from BookInformatica where BookName='" + BF.BookName + "'";
            SqlDataReader str = DBHelper.Getdata(sql1);
            var i = 1;
            while (str.Read())
            {
                i = (int)str["BookId"];
            }
            string sql2 = string.Format(" insert into BookNumber(BookCount,BookId)values('{0}','{1}')", BF.BookCount, i);
            DBHelper.Adddata(sql2);
        }                                                                                                                                                                             
        //修改图书类别信息
        public static List<BookType> UpdateTypeMessage(BookType bt)
        {
            string sql = string.Format("select * from BookType where TypeID={0}", bt.TypeID);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookType> list = new List<BookType>();
            while (sdr.Read())
            {
                BookType b = new BookType();
                b.TypeID = (int)sdr["TypeID"];
                b.TypeName = (string)sdr["TypeName"];
                b.state = (int)sdr["state"];
                b.Note = (string)sdr["Note"].ToString();
                list.Add(b);
            }
            return list;
        }
        //修改图书类别信息
        public static void UpdateType(BookType BT)
        {
            string sql = string.Format("update BookType set TypeName='{0}' ,Note='{1}',state='{2}' where TypeID='{3}'", BT.TypeName, BT.Note, BT.state, BT.TypeID);
            DBHelper.Adddata(sql);
        }
        //图书信息展示
        public static List<BookInformatica> SeleteBookMessage()
        {
            string sql = string.Format("select Top(20)*from BookInformatica");
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> UI = new List<Entity.BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica Bf = new BookInformatica();
                Bf.BookId = (int)sdr["BookId"];
                Bf.BookName = (string)sdr["BookName"];
                Bf.BookAuthor = (string)sdr["BookAuthor"];
                Bf.TypeID = (int)sdr["TypeID"];
                Bf.BookPrice = (decimal)sdr["BookPrice"];
                Bf.BookTime = (DateTime)sdr["BookTime"];
                Bf.BookState = (int)sdr["BookState"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        public static List<BookInformatica> SeleteBookMessages(BookInformatica bf)
        {
            string sql = string.Format("select *from BookInformatica where BookName like'%{0}%'",bf.BookName);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> UI = new List<Entity.BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica Bf = new BookInformatica();
                Bf.BookId = (int)sdr["BookId"];
                Bf.BookName = (string)sdr["BookName"];
                Bf.BookAuthor = (string)sdr["BookAuthor"];
                Bf.TypeID = (int)sdr["TypeID"];
                Bf.BookPrice = (decimal)sdr["BookPrice"];
                Bf.BookTime = (DateTime)sdr["BookTime"];
                Bf.BookState = (int)sdr["BookState"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        //修改图书信息
        public static List<BookInformatica> UpdateBooks(BookInformatica bf)
        {
            string sql = string.Format("select*from BookInformatica b ,BookNumber n  where b.BookId=n.BookId and b.BookId='{0}'", bf.BookId);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> UI = new List<Entity.BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica Bf = new BookInformatica();
                Bf.BookId = (int)sdr["BookId"];
                Bf.BookName = (string)sdr["BookName"];
                Bf.BookAuthor = (string)sdr["BookAuthor"];
                Bf.BookPrice = (decimal)sdr["BookPrice"];
                Bf.BookPress = (string)sdr["BookPress"];
                Bf.BookDescribe = (string)sdr["BookDescribe"];
                Bf.BookTime = (DateTime)sdr["BookTime"];
                Bf.BookState = (int)sdr["BookState"];
                Bf.BookCount = (int)sdr["BookCount"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        //修改图书信息
        public static void UpdateBooksMessage(BookInformatica bf)
        {
            string sql = string.Format("update BookInformatica set BookPrice='{0}',BookState='{1}',BookDescribe='{2}'where BookId='{3}'",bf.BookPrice,bf.BookState,bf.BookDescribe,bf.BookId);
            DBHelper.Adddata(sql);
            string sql2 = string.Format("update BookNumber set BookCount='{0}' where BookId='{1}'", bf.BookCount,bf.BookId);
            DBHelper.Adddata(sql2);
        }
        //模糊查询已上架书籍
        public static List<BookInformatica> SBooks()
        {
            string sql = string.Format("select Top(20)*from BookInformatica where BookState='1'");
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> UI = new List<Entity.BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica Bf = new BookInformatica();
                Bf.BookId = (int)sdr["BookId"];
                Bf.BookName = (string)sdr["BookName"];
                Bf.BookAuthor = (string)sdr["BookAuthor"];
                Bf.TypeID = (int)sdr["TypeID"];
                Bf.BookPrice = (decimal)sdr["BookPrice"];
                Bf.BookTime = (DateTime)sdr["BookTime"];
                Bf.BookState = (int)sdr["BookState"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        //模糊查询已下架书籍
        public static List<BookInformatica> SBook()
        {
            string sql = string.Format("select  Top(20)*from BookInformatica where BookState='2'");
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> UI = new List<Entity.BookInformatica>();
            while (sdr.Read()) 
            {
                BookInformatica Bf = new BookInformatica();
                Bf.BookId = (int)sdr["BookId"];
                Bf.BookName = (string)sdr["BookName"];
                Bf.BookAuthor = (string)sdr["BookAuthor"];
                Bf.TypeID = (int)sdr["TypeID"];
                Bf.BookPrice = (decimal)sdr["BookPrice"];
                Bf.BookTime = (DateTime)sdr["BookTime"];
                Bf.BookState = (int)sdr["BookState"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        //图书信息反馈
        public static List<FeedBack> SelectFeedBook() 
        {
            string sql = string.Format("select*from FeedBack");
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<FeedBack> UI = new List<Entity.FeedBack>();
            while (sdr.Read())
            {
                FeedBack Bf = new FeedBack();
                Bf.FeedBackId = (int)sdr["FeedBackId"];
                Bf.UserId = (int)sdr["UserId"];
                Bf.BookId = (int)sdr["BookId"];
                Bf.FeedBackValue = (string)sdr["FeedBackValue"];
                Bf.FeedBackDate = (DateTime)sdr["FeedBackDate"];
                Bf.FeedBackState = (int)sdr["FeedBackState"];
                UI.Add(Bf);
            }
            sdr.Close();
            return UI;
        }
        //改变反馈状态
        public static void UpdateFeedBack(FeedBack f)
        {
            string sql = string.Format("select * from FeedBack where FeedBackId='{0}'", f.FeedBackId);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            int i = 0;
            while (sdr.Read())
            {
                i = (int)sdr["FeedBackState"];
            }
            sdr.Close();
            if (i == 1)
            {
                string sql1 = string.Format("update FeedBack set FeedBackState='2' where FeedBackId={0}", f.FeedBackId);
                DBHelper.Adddata(sql1);
            }
        }
    }
}
